package com.mysmartphone.bd;

import java.util.ArrayList;
import java.util.HashMap;

import com.mysmartphone.tools.Tools;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MySQLiteHelper extends SQLiteOpenHelper {

	Context context;
	
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "MYSMARTPHONE";

	
	private static final String TABLE_SETTINGS = "SETTINGS";
		
	private static final String TABLE_SAVE = "SAVE";
	
	private static final String TABLE_SAVE_TYPE = "SAVE_TYPE";
	
	private static final String TABLE_SAVE_RESOURCES = "SAVE_RESOURCES";
	
	private static final String TABLE_SAVE_SETTINGS = "SETTINGS";

	private static final String FIELD_ID_SAVE = "ID_SAVE";
	private static final String FIELD_ID_SAVE_TYPE = "ID_SAVE_TYPE";
	private static final String FIELD_ID_SAVE_RESOURCES = "ID_SAVE_RESOURCES";
	private static final String FIELD_ID_PHONE = "ID_PHONE";
	private static final String FIELD_DESCRIPTION = "DESCRIPTION";
	private static final String FIELD_ID_SETTING ="ID_SETTING";
	private static final String FIELD_COIN="COIN";
	private static final String FIELD_COIN_DES="COIN_DES";
	
	

	public static final int FAVORITE = 1;
	public static final int MY_SEARCHES = 2;
	public static final int MY_COMPARISONS = 3;
	
	public static final String FAVORITE_DES = "FAVORITOS";
	public static final String MY_SEARCHES_DES = "MY SEARCHES";
	public static final String MY_COMPARISONS_DES = "MY COMPARISONS";

	
	public static final String QUERY_TRY_NAME_FAVORITE = "SELECT "+FIELD_DESCRIPTION+" FROM "+TABLE_SAVE+" WHERE "+FIELD_DESCRIPTION+" = '?' AND "+FIELD_ID_SAVE_TYPE+" = 1";
	
	public MySQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		ArrayList<String> tables = new ArrayList<String>();
		String CREATE_SAVE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE + "(ID_SAVE INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ID_SAVE_TYPE INTEGER,DESCRIPTION TEXT)";
		tables.add(CREATE_SAVE_TABLE);
		
		String CREATE_SAVE_TYPE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE_TYPE + "(ID_SAVE_TYPE INTEGER PRIMARY KEY NOT NULL, DESCRIPTION TEXT)";
		tables.add(CREATE_SAVE_TYPE_TABLE);
		
		String CREATE_SAVE_RESOURCES = "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE_RESOURCES + "(ID_SAVE_RESOURCES INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ID_SAVE INTEGER,ID_PHONE INTEGER)";
		tables.add(CREATE_SAVE_RESOURCES);
		
		String CREATE_SETTINGS= "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE_SETTINGS + "(ID_SETTING INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, COIN INTEGER,COIN_DES TEXT)";
		tables.add(CREATE_SETTINGS);

		for (String query:tables) {
			db.execSQL(query);
		}
		
		
		//inserts
		ContentValues values = new ContentValues();
		values.put(FIELD_ID_SAVE_TYPE, 1);
		values.put(FIELD_DESCRIPTION,"FAVORITES"); 
		db.insert(TABLE_SAVE_TYPE, null, values);
		
		
		values = new ContentValues();
		values.put(FIELD_ID_SAVE_TYPE, 2);
		values.put(FIELD_DESCRIPTION, "MY SEARCHES"); 
		db.insert(TABLE_SAVE_TYPE, null, values);
		
		values = new ContentValues();
		values.put(FIELD_ID_SAVE_TYPE, 3);
		values.put(FIELD_DESCRIPTION, "MY COMPARISONS"); 
		// Inserting Row
		db.insert(TABLE_SAVE_TYPE, null, values);
		
		//settings
		String actualLanguage = Tools.getLanguage(context.getResources());
		if(actualLanguage.equals("us")){
			values = new ContentValues();
//			values.put(FIELD_ID_SAVE_TYPE, 3);
//			values.put(FIELD_DESCRIPTION, "MY COMPARISONS"); 
		}else{
			values = new ContentValues();
			//values.put(FIELD_ID_SAVE_TYPE, 3);
			//values.put(FIELD_DESCRIPTION, "MY COMPARISONS"); 
		}
				
//		db.close();
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		//db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

		// Create tables again
		//onCreate(db);
	}
	
	public void addSave(String description,int save_type) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(FIELD_ID_SAVE, "");
		values.put(FIELD_ID_SAVE_TYPE, save_type);
		values.put(FIELD_DESCRIPTION, description);
	
		// Inserting Row
		db.insert(TABLE_SAVE, null, values);

		db.close();
	}

	public void addFavorites(Long idPhone) {
	
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		//values.put(FIELD_ID_SAVE, "");
		values.put(FIELD_ID_SAVE_TYPE, FAVORITE);
		values.put(FIELD_DESCRIPTION,FAVORITE_DES); 
		long idSave = db.insert(TABLE_SAVE, null, values);
		
		values = new ContentValues();
		values.put(FIELD_ID_SAVE,  idSave);
		values.put(FIELD_ID_PHONE,idPhone); 
		idSave = db.insert(TABLE_SAVE_RESOURCES, null, values);
				
		this.getIdsFavorites();
		db.close();
	}
	
	public void deleteFavorites(Long idPhone,Long idSave) {
		
		SQLiteDatabase db = this.getWritableDatabase();
		

		db.delete(TABLE_SAVE_RESOURCES, FIELD_ID_PHONE +"=" +idPhone, null);
		db.delete(TABLE_SAVE, FIELD_ID_SAVE +"=" +idPhone, null);
		db.close();
	}
	
	
	public HashMap<String,Long[]> getIdsFavorites(){
		
		HashMap<String,Long[]> listIdFavorites = new HashMap<String,Long[]>();
		SQLiteDatabase db = this.getReadableDatabase();
		String query ="SELECT SR."+FIELD_ID_PHONE+",SR."+FIELD_ID_SAVE+" FROM SAVE_RESOURCES SR,SAVE S,SAVE_TYPE ST WHERE S.ID_SAVE = SR.ID_SAVE AND S.ID_SAVE_TYPE = ST.ID_SAVE_TYPE AND ST.ID_SAVE_TYPE ="+FAVORITE;
		Cursor c = db.rawQuery(query, null);
		Long [] ids = null;
		
		if(c.moveToFirst()){
			do{				
				ids= new Long[2];
				ids[0] =c.getLong(0);
				ids[1] = c.getLong(1);
				listIdFavorites.put(String.valueOf(c.getLong(0)),ids);
			}while(c.moveToNext());
		}
		db.close();
		
		return listIdFavorites;
	}
	
	
	public void deleteDatabase(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_SAVE_RESOURCES);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_SAVE);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_SAVE_TYPE);
		//db.execSQL("delete from "+TABLE_SAVE_RESOURCES);
		createBD();

		
		db.close();
	}
	private void createBD() {
		SQLiteDatabase db = this.getWritableDatabase();
		ArrayList<String> tables = new ArrayList<String>();
		String CREATE_SAVE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE + "(ID_SAVE INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ID_SAVE_TYPE INTEGER,DESCRIPTION TEXT)";
		tables.add(CREATE_SAVE_TABLE);
		
		String CREATE_SAVE_TYPE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE_TYPE + "(ID_SAVE_TYPE INTEGER PRIMARY KEY NOT NULL, DESCRIPTION TEXT)";
		tables.add(CREATE_SAVE_TYPE_TABLE);
		
		String CREATE_SAVE_RESOURCES = "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE_RESOURCES + "(ID_SAVE_RESOURCES INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ID_SAVE INTEGER,ID_PHONE INTEGER)";
		tables.add(CREATE_SAVE_RESOURCES);
		
		String CREATE_SETTINGS= "CREATE TABLE IF NOT EXISTS " + TABLE_SAVE_SETTINGS + "(ID_SETTING INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, COIN INTEGER,COIN_DES TEXT)";
		tables.add(CREATE_SETTINGS);

		for (String query:tables) {
			db.execSQL(query);
		}
		
		
		//inserts
		ContentValues values = new ContentValues();
		values.put(FIELD_ID_SAVE_TYPE, 1);
		values.put(FIELD_DESCRIPTION,"FAVORITES"); 
		db.insert(TABLE_SAVE_TYPE, null, values);
		
		
		values = new ContentValues();
		values.put(FIELD_ID_SAVE_TYPE, 2);
		values.put(FIELD_DESCRIPTION, "MY SEARCHES"); 
		db.insert(TABLE_SAVE_TYPE, null, values);
		
		values = new ContentValues();
		values.put(FIELD_ID_SAVE_TYPE, 3);
		values.put(FIELD_DESCRIPTION, "MY COMPARISONS"); 
		// Inserting Row
		db.insert(TABLE_SAVE_TYPE, null, values);
		
		//settings
		String actualLanguage = Tools.getLanguage(context.getResources());
		if(actualLanguage.equals("us")){
			values = new ContentValues();
//			values.put(FIELD_ID_SAVE_TYPE, 3);
//			values.put(FIELD_DESCRIPTION, "MY COMPARISONS"); 
		}else{
			values = new ContentValues();
			//values.put(FIELD_ID_SAVE_TYPE, 3);
			//values.put(FIELD_DESCRIPTION, "MY COMPARISONS"); 
		}
		
	}

	public void addSaveResource(ArrayList <Long> listIdPhone,int idSave) {
		SQLiteDatabase db = this.getWritableDatabase();
		for (int i = 0; i < listIdPhone.size(); i++) {
			ContentValues values = new ContentValues();
			values.put(FIELD_ID_SAVE, idSave);
			values.put(FIELD_ID_PHONE, Integer.parseInt(listIdPhone.get(i)+""));		
			db.insert(TABLE_SAVE, null, values);
		}
		
		db.close();
		
	}
	
	public boolean query(String query){
		boolean valid = false;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery(query, null);
				
		valid = c.moveToFirst();
		db.close(); // Closing database connection
		return valid;
	}
	
	public long getTotalCategory(int category){
		SQLiteDatabase db = this.getReadableDatabase();
		String query ="SELECT COUNT(SR.ID_SAVE_RESOURCES) M FROM SAVE_RESOURCES SR,SAVE S,SAVE_TYPE ST WHERE S.ID_SAVE = SR.ID_SAVE AND S.ID_SAVE_TYPE = ST.ID_SAVE_TYPE AND ST.ID_SAVE_TYPE ="+category;
		Cursor c = db.rawQuery(query, null);
		
		long total = 0;
		if(c.moveToFirst()){
			total = c.getInt(0);
		}
		
		db.close();
		
		return total;
	}
	
	public ArrayList<Long> getIdPhoneByCategory(int category){
		SQLiteDatabase db = this.getReadableDatabase();
		String query ="SELECT "+FIELD_ID_PHONE+" FROM SAVE_RESOURCES SR,SAVE S,SAVE_TYPE ST WHERE S.ID_SAVE = SR.ID_SAVE AND S.ID_SAVE_TYPE = ST.ID_SAVE_TYPE AND ST.ID_SAVE_TYPE ="+category;
		Cursor c = db.rawQuery(query, null);

		ArrayList<Long> ids = new ArrayList<Long> ();
		if(c.moveToFirst()){
			while(c.moveToNext()){
				ids.add(c.getLong(0));
			}
		}
		
		db.close();
		
		return ids;
	}
}
